const express = require('express');
const router = express.Router();
const { pool } = require('../config/db');
const auth = require('../middleware/auth');

// 获取请假列表
router.get('/', auth, async (req, res) => {
  try {
    console.log('收到获取请假列表请求，参数:', req.query);
    
    // 检查权限
    if (!req.user) {
      console.log('用户未认证');
      return res.status(401).json({ message: '未认证' });
    }
    
    if (req.user.role !== 'admin') {
      console.log('用户权限不足:', req.user.role);
      return res.status(403).json({ message: '权限不足' });
    }
    
    const { status, page = 1, pageSize = 10 } = req.query;
    const offset = (page - 1) * pageSize;
    
    // 构建查询条件
    let whereClause = '';
    const params = [];
    
    if (status && status !== 'all') {
      whereClause = ' WHERE l.status = ?';
      params.push(status);
    }
    
    console.log('查询条件:', whereClause, '参数:', params);
    
    // 先查询 leaves 表的结构
    const [columns] = await pool.query('SHOW COLUMNS FROM leaves');
    console.log('leaves表结构:', columns.map(col => col.Field));
    
    const [leaves] = await pool.query(`
      SELECT 
        l.id,
        l.studentId,
        u.name as studentName,
        u.userId as studentNumber,
        c.className,
        l.type,
        l.startDate,
        l.endDate,
        l.startDate as startTime,  
        l.endDate as endTime,       
        l.days,
        l.reason,
        l.status,
        l.applyTime,
        IFNULL(a.name, '') as approver,
        l.approveTime,
        l.approveRemark as rejectReason
      FROM leaves l
      JOIN users u ON l.studentId = u.id
      LEFT JOIN classes c ON u.classId = c.id
      LEFT JOIN users a ON l.approverId = a.id
      ${whereClause}
      ORDER BY l.applyTime DESC
      LIMIT ?, ?
    `, [...params, offset, parseInt(pageSize)]);
    
    console.log('查询到的请假记录数量:', leaves.length);
    
    // 获取总记录数
    const [countResult] = await pool.query(`
      SELECT COUNT(*) as total
      FROM leaves l
      JOIN users u ON l.studentId = u.id
      LEFT JOIN classes c ON u.classId = c.id
      ${whereClause}
    `, params);
    
    const total = countResult[0].total;
    
    res.json({
      list: leaves,
      pagination: {
        current: parseInt(page),
        pageSize: parseInt(pageSize),
        total
      }
    });
  } catch (error) {
    console.error('获取请假列表失败:', error);
    // 返回更详细的错误信息
    res.status(500).json({ 
      message: '服务器错误', 
      error: error.message,
      stack: process.env.NODE_ENV === 'development' ? error.stack : undefined
    });
  }
});

// 导出请假数据 - 移到前面，确保在/:id路由之前
router.get('/export', auth, async (req, res) => {
  try {
    console.log('收到导出请假数据请求，参数:', req.query);
    
    // 检查权限
    if (req.user.role !== 'admin') {
      return res.status(403).json({ message: '权限不足' });
    }
    
    const { status, startDate, endDate } = req.query;
    
    // 构建查询条件
    let whereClause = '';
    const params = [];
    
    const conditions = [];
    
    if (status && status !== 'all') {
      conditions.push('l.status = ?');
      params.push(status);
    }
    
    if (startDate) {
      conditions.push('l.startDate >= ?');
      params.push(startDate);
    }
    
    if (endDate) {
      conditions.push('l.endDate <= ?');
      params.push(endDate);
    }
    
    if (conditions.length > 0) {
      whereClause = ' WHERE ' + conditions.join(' AND ');
    }
    
    console.log('导出查询条件:', whereClause, '参数:', params);
    
    // 获取请假列表
    const [leaves] = await pool.query(`
      SELECT 
        l.id,
        u.name as studentName,
        u.userId as studentNumber,
        c.className,
        l.type,
        l.startDate,
        l.endDate,
        l.days,
        l.reason,
        CASE 
          WHEN l.status = 'pending' THEN '待审批'
          WHEN l.status = 'approved' THEN '已批准'
          WHEN l.status = 'rejected' THEN '已拒绝'
          ELSE l.status
        END as status,
        l.applyTime,
        IFNULL(a.name, '') as approver,
        l.approveTime,
        l.approveRemark as rejectReason
      FROM leaves l
      JOIN users u ON l.studentId = u.id
      LEFT JOIN classes c ON u.classId = c.id
      LEFT JOIN users a ON l.approverId = a.id
      ${whereClause}
      ORDER BY l.applyTime DESC
    `, params);
    
    console.log(`找到${leaves.length}条请假记录用于导出`);
    
    // 创建Excel文件
    const Excel = require('exceljs');
    const path = require('path');
    const fs = require('fs');
    
    // 生成文件名，包含筛选条件
    let fileName = '请假管理';
    if (status) {
      fileName += `_${status === 'pending' ? '待审批' : status === 'approved' ? '已批准' : '已拒绝'}`;
    }
    if (startDate && endDate) {
      fileName += `_${startDate}至${endDate}`;
    } else if (startDate) {
      fileName += `_从${startDate}起`;
    } else if (endDate) {
      fileName += `_截至${endDate}`;
    }
    fileName += `_${new Date().toISOString().slice(0, 10)}.xlsx`;
    
    const filePath = path.join(__dirname, '../public/exports', fileName);
    
    // 确保目录存在
    if (!fs.existsSync(path.join(__dirname, '../public/exports'))) {
      fs.mkdirSync(path.join(__dirname, '../public/exports'), { recursive: true });
    }
    
    // 创建工作簿和工作表
    const workbook = new Excel.Workbook();
    const worksheet = workbook.addWorksheet('请假记录');
    
    // 设置列
    worksheet.columns = [
      { header: '学生姓名', key: 'studentName', width: 15 },
      { header: '学号', key: 'studentNumber', width: 15 },
      { header: '班级', key: 'className', width: 20 },
      { header: '请假类型', key: 'type', width: 10 },
      { header: '开始时间', key: 'startDate', width: 20 },
      { header: '结束时间', key: 'endDate', width: 20 },
      { header: '天数', key: 'days', width: 10 },
      { header: '请假原因', key: 'reason', width: 30 },
      { header: '状态', key: 'status', width: 10 },
      { header: '申请时间', key: 'applyTime', width: 20 },
      { header: '审批人', key: 'approver', width: 15 },
      { header: '审批时间', key: 'approveTime', width: 20 },
      { header: '拒绝原因', key: 'rejectReason', width: 30 }
    ];
    
    // 设置表头样式
    worksheet.getRow(1).font = { bold: true };
    worksheet.getRow(1).alignment = { vertical: 'middle', horizontal: 'center' };
    
    // 添加数据
    worksheet.addRows(leaves);
    
    // 保存文件
    await workbook.xlsx.writeFile(filePath);
    
    console.log('Excel文件已生成:', filePath);
    
    // 记录导出操作
    await pool.query(`
      INSERT INTO logs (userId, userName, userRole, action, target, content, ip)
      VALUES (?, ?, ?, 'export', 'leaves', ?, ?)
    `, [req.user.id, req.user.name || '管理员', req.user.role, `导出请假记录(${leaves.length}条)`, req.ip]);
    
    // 设置响应头，直接下载文件
    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.setHeader('Content-Disposition', `attachment; filename=${encodeURIComponent(fileName)}`);
    
    // 发送文件
    fs.createReadStream(filePath).pipe(res);
    
  } catch (error) {
    console.error('导出请假数据失败:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 获取请假详情
router.get('/:id', auth, async (req, res) => {
  try {
    // 检查权限
    if (req.user.role !== 'admin') {
      return res.status(403).json({ message: '权限不足' });
    }
    
    const { id } = req.params;
    
    // 获取请假详情
    const [leaves] = await pool.query(`
      SELECT 
        l.id,
        l.studentId,
        u.name as studentName,
        u.userId as studentNumber,
        c.className,
        l.type,
        l.startDate,
        l.endDate,
        l.days,
        l.reason,
        l.status,
        l.applyTime,
        IFNULL(a.name, '') as approver,
        l.approveTime,
        l.approveRemark as rejectReason,
        l.attachment as attachments
      FROM leaves l
      JOIN users u ON l.studentId = u.id
      LEFT JOIN classes c ON u.classId = c.id
      LEFT JOIN users a ON l.approverId = a.id
      WHERE l.id = ?
    `, [id]);
    
    if (leaves.length === 0) {
      return res.status(404).json({ message: '请假记录不存在' });
    }
    
    res.json(leaves[0]);
  } catch (error) {
    console.error('获取请假详情失败:', error);
    res.status(500).json({ message: '服务器错误' });
  }
});

// 更新请假状态
router.put('/:id/status', auth, async (req, res) => {
  try {
    // 检查权限
    if (req.user.role !== 'admin') {
      return res.status(403).json({ message: '权限不足' });
    }
    
    const { id } = req.params;
    const { status, reason } = req.body;
    
    if (!['approved', 'rejected'].includes(status)) {
      return res.status(400).json({ message: '无效的状态值' });
    }
    
    // 检查请假记录是否存在
    const [leaveCheck] = await pool.query('SELECT * FROM leaves WHERE id = ?', [id]);
    
    if (leaveCheck.length === 0) {
      return res.status(404).json({ message: '请假记录不存在' });
    }
    
    // 打印请假记录信息，帮助调试
    console.log('请假记录:', leaveCheck[0]);
    console.log('当前用户:', req.user);
    
    // 更新请假状态
    await pool.query(`
      UPDATE leaves 
      SET status = ?, 
          approverId = ?, 
          approveTime = NOW(), 
          approveRemark = ?
      WHERE id = ?
    `, [status, req.user.id, status === 'rejected' ? reason : null, id]);
    
    // 确保用户名不为空
    const userName = req.user.name || '管理员';
    
    // 记录操作日志
    await pool.query(`
      INSERT INTO logs (userId, userName, userRole, action, target, content, ip)
      VALUES (?, ?, ?, ?, ?, ?, ?)
    `, [
      req.user.id, 
      userName,
      req.user.role, 
      status === 'approved' ? 'approve' : 'reject', 
      'leave', 
      `${status === 'approved' ? '批准' : '拒绝'}请假申请(ID:${id})`, 
      req.ip
    ]);
    
    // 发送消息通知学生
    const studentId = leaveCheck[0].studentId;
    const messageTitle = status === 'approved' ? '请假申请已批准' : '请假申请被拒绝';
    const messageContent = status === 'approved' 
      ? `您的请假申请已被批准。请假时间：${leaveCheck[0].startDate} 至 ${leaveCheck[0].endDate}。` 
      : `您的请假申请被拒绝。原因：${reason || '无'}。请假时间：${leaveCheck[0].startDate} 至 ${leaveCheck[0].endDate}。`;
    
    // 使用正确的字段名插入消息
    await pool.query(`
      INSERT INTO messages (senderId, senderName, receiverId, title, content, type)
      VALUES (?, ?, ?, ?, ?, 'personal')
    `, [req.user.id, userName, studentId, messageTitle, messageContent]);
    
    res.json({ message: '操作成功' });
  } catch (error) {
    console.error('更新请假状态失败:', error);
    res.status(500).json({ 
      message: '服务器错误',
      error: error.message,
      stack: process.env.NODE_ENV === 'development' ? error.stack : undefined
    });
  }
});


module.exports = router;